﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml.Linq;
using Microsoft.Office.Tools.Excel;
using Microsoft.VisualStudio.Tools.Applications.Runtime;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using System.Globalization;
using Microsoft.Office.Interop.Excel;
using System.IO;

namespace OffInvoiceExcelTemplate
{
    public partial class Hoja3
    {
        Hoja1 h1;
        Hoja2 h2;
        
        private void Hoja3_Startup(object sender, System.EventArgs e)
        {
            h1 = Globals.Hoja1;
            h2 = Globals.Hoja2;
        
        }

        private void Hoja3_Shutdown(object sender, System.EventArgs e)
        {
        }

        #region VSTO Designer generated code

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InternalStartup()
        {
            this.CORR_TaxPeriod_StartDate.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.CORR_TaxPeriod_StartDate_Change);
            this.CORR_TaxPeriod_EndDate.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.CORR_TaxPeriod_EndDate_Change);
            this.InvIssue_IssueDate.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.InvIssue_IssueDate_Change);
            this.comodinV.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(this.comodinV_Change);
            this.Startup += new System.EventHandler(this.Hoja3_Startup);
            this.Shutdown += new System.EventHandler(this.Hoja3_Shutdown);

        }

        #endregion

       
        private void setCbxDisplayName(string formula,string displayNameRange)
        {
            try
            {
                if (h1.culture.Name.ToLower().Equals("en-us"))
                {
                   formula= formula.Replace("INDICE", "INDEX");
                   formula = formula.Replace("COINCIDIR", "MATCH");
                }
                Excel.Workbook wb = this.Application.ActiveWorkbook;
                Excel.Worksheet ws = wb.Sheets[3];
                h2.edit(wb, false);
                Excel.Range rng = ws.get_Range("comodinV", missing);
                Excel.Range rng2 = ws.get_Range("comodinR", missing);
                string valor = Convert.ToChar(34) + rng.Value2 + Convert.ToChar(34);
                formula = formula.Replace("valor", valor);
                rng2.FormulaLocal = formula;
                Excel.Worksheet ws2 = this.Application.Sheets[1];
                Excel.Range rng3 = ws2.get_Range(displayNameRange, missing);
                rng3.Value2 = rng2.Value2;
                h2.edit(wb, true);
            }
            catch (Exception) { }
        }

        private void setDates(Microsoft.Office.Tools.Excel.Controls.DateTimePicker dtp,string date)
        {
            try
            {

                Excel.Workbook wb = this.Application.ActiveWorkbook;
                Excel.Worksheet ws = wb.Sheets[3];
                h2.edit(wb, false);
                CultureInfo culture = h1.culture;
                //dtp.Value = DateTime.Parse(comodinV.Text.ToString(),culture);
                dtp.Value = DateTime.Parse(date, culture);
                h2.edit(wb, true);
            }
            catch (Exception) { }
        }



        

        private void comodinV_Change(Excel.Range Target)
        {
            if (comodinN.Text.Equals("CORR_ReasonDescription"))
            {
                string formula = "=INDICE(CORR_ReasonDescriptionNandV;COINCIDIR(valor;CORR_ReasonDescriptionValue;0);1)";
               
              
                setCbxDisplayName(formula,"c_rdesc");
            }

            if (comodinN.Text.Equals("CORR_CorrectionMethodDescription"))
            {
                string formula = "=INDICE(CORR_CorrectionMethodDescriptionNAndV;COINCIDIR(valor;CORR_CorrectionMethodDescriptionValue;0);1)";
                setCbxDisplayName(formula, "c_ccmdesc");
            }

            if (comodinN.Text.Equals("IH_InvoiceClass"))
            {
                string formula = "=INDICE(InvoiceTypeNandV;COINCIDIR(valor;InvoiceTypeValue;0);1)";
                setCbxDisplayName(formula, "inv_class");
            }

            if (comodinN.Text.Equals("InvoiceIssuerType"))
            {
                string formula = "=INDICE(InvoiceIssuerNAndV;COINCIDIR(valor;InvoiceIssuerValue;0);1)";
                setCbxDisplayName(formula, "inv_isst");
            }

            if (comodinN.Text.Equals("SP_CountryCode"))
            {
                string formula = "=INDICE(countrycodeslist;COINCIDIR(valor;CountryValues;0);1)";
                setCbxDisplayName(formula, "sp_cc");
            }

            if (comodinN.Text.Equals("BP_CountryCode"))
            {
                string formula = "=INDICE(countrycodeslist;COINCIDIR(valor;CountryValues;0);1)";
                setCbxDisplayName(formula, "bp_cc");
            }

            if (comodinN.Text.Equals("TH_CountryCode"))
            {
                string formula = "=INDICE(countrycodeslist;COINCIDIR(valor;CountryValues;0);1)";
                setCbxDisplayName(formula, "th_cc");
            }

            if (comodinN.Text.Equals("SP_PersonTypeCode"))
            {
                string formula ="=INDICE(PersonTypeNandV;COINCIDIR(valor;PersonTypeValue;0);1)";
                setCbxDisplayName(formula, "sp_ptc");
            }
            if (comodinN.Text.Equals("BP_PersonTypeCode"))
            {
                string formula = "=INDICE(PersonTypeNandV;COINCIDIR(valor;PersonTypeValue;0);1)";
                setCbxDisplayName(formula, "bp_ptc");
            }
            if (comodinN.Text.Equals("TH_PersonTypeCode"))
            {
                string formula = "=INDICE(PersonTypeNandV;COINCIDIR(valor;PersonTypeValue;0);1)";
                setCbxDisplayName(formula, "th_ptc");
            }
            if (comodinN.Text.Equals("SP_ResidenceTypeCode"))
            {
                string formula = "=INDICE(ResidentTypeNandV;COINCIDIR(valor;ResidentTypeValue;0);1)";
                setCbxDisplayName(formula, "sp_rtc");
            }
            if (comodinN.Text.Equals("BP_ResidenceTypeCode"))
            {
                string formula = "=INDICE(ResidentTypeNandV;COINCIDIR(valor;ResidentTypeValue;0);1)";
                setCbxDisplayName(formula, "bp_rtc");
            }
            if (comodinN.Text.Equals("TH_ResidenceTypeCode"))
            {
                string formula = "=INDICE(ResidentTypeNandV;COINCIDIR(valor;ResidentTypeValue;0);1)";
                setCbxDisplayName(formula, "th_rtc");
            }

            if (comodinN.Text.Equals("InvIssue_IssueDate"))
            {
                setDates(h1.dtp_inv_issd, comodinV.Text.ToString());
            }

            if (comodinN.Text.Equals("CORR_TaxPeriod_StartDate"))
            {

                setDates(h1.dtp_c_taxpsd, comodinV.Text.ToString());
                
            }
            if (comodinN.Text.Equals("CORR_TaxPeriod_EndDate"))
            {

                setDates(h1.dtp_c_taxped, comodinV.Text.ToString());
            }


        }

        private void InvIssue_IssueDate_Change(Range Target)
        {
            //setDates(h1.dtp_inv_issd, InvIssue_IssueDate.Text.ToString());
        }

        private void CORR_TaxPeriod_StartDate_Change(Range Target)
        {
            //setDates(h1.dtp_c_taxpsd, CORR_TaxPeriod_StartDate.Text.ToString());
        }

        private void CORR_TaxPeriod_EndDate_Change(Range Target)
        {
            //setDates(h1.dtp_c_taxped, CORR_TaxPeriod_EndDate.Text.ToString());
        }


        
   


    }
}
